package com.hsoft.zhxy.app.xtgl_02_01.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.hsoft.zhxy.app.common.model.entity.*;
import com.hsoft.zhxy.app.xtgl_02_01.model.query.JsglQuery;
import com.hsoft.zhxy.app.xtgl_02_01.model.vo.YhglVO;
import com.hsoft.zhxy.app.xtgl_02_01.model.vo.YhjsVO;
import com.hsoft.zhxy.app.xtgl_02_01.model.vo.YhjsmxVO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * All rights Reserved, Designed By 哈尔滨工业大学软件工程股份有限公司
 *
 * @Title: 角色 DAO层接口
 * @Package
 * @Description:
 * @author: system
 * @date: 2020-10-23
 */
@Mapper
@Repository
public interface JcqxJsMapper extends BaseMapper<JcqxJs> {

    @Select("select count(yh.yhid) num\n" +
            "        from jcqx_yh yh, jcqx_yhjs yhjs\n" +
            "        where yhjs.jsid = #{jsid}\n" +
            "        and (yh.yhid = yhjs.yhid or\n" +
            "        (yh.sfms is not null and yhjs.sfmsjh like ('%'||yh.sfms||'%') ))")
    Integer selectCountYh(JcqxJs js);

    @Select("select count(b.jsid)\n" +
            "        from\n" +
            "        jcqx_js a\n" +
            "        left join\n" +
            "        jcqx_js b on a.jsid=b.fjsid\n" +
            "        where a.jsid=#{jsid}")
    Integer findCountZycdjs(JcqxJs jcqxJs);

    @Select("select b.gnzyid,b.gnzymc from jcqx_jszy a,jcqx_gnzy b\n" +
            "        where a.jsid=#{jsid} and a.gnzyid=b.gnzyid")
    List<JcqxGnzy> findCkeckedYyzy(JcqxJs jcqxJs);

    @Select("<script> " + "select yh.yhid,yh.xm,yhjs.jsid,yhjs.sfmsjh\n" +
            "        from jcqx_yh yh, jcqx_yhjs yhjs\n" +
            "        where yhjs.jsid = #{jsglQuery.jsid}\n" +
            "        and (yh.yhid = yhjs.yhid or\n" +
            "        (yh.sfms is not null and yhjs.sfmsjh like ('%'||yh.sfms||'%')))\n" +
            "        <if test=\"jsglQuery.yhzt != null\">\n" +
            "            and yh.yhzt = #{jsglQuery.yhzt}\n" +
            "        </if>\n" +
            "        <if test=\"jsglQuery.ppfs == 0\">\n" +
            "            and yhjs.yhid is not null\n" +
            "        </if>\n" +
            "        <if test=\"jsglQuery.ppfs == 1\">\n" +
            "            and yh.sfms is not null and yhjs.yhid is null\n" +
            "        </if>\n" +
            "        <if test=\"jsglQuery.idxm != null and jsglQuery.idxm != ''\">\n" +
            "            and (yh.xm like '%${jsglQuery.idxm}%' or yh.yhid like '%${jsglQuery.idxm}%')\n" +
            "        </if>\n" +
            "        order by yh.yhid asc" + "</script>")
    IPage<YhjsVO> findJsxyhlist(Page<JsglQuery> pageObj, @Param("jsglQuery") JsglQuery jsglQuery);

    @Select("select mx.id,mx.yhid,mx.jsid,mx.bmm,mx.kzzd1,mx.kzzd2,mx.kzzd3,mx.kzzd4,bm.bmmc\n" +
            "\t\tfrom jcqx_yhjsmx mx,jcqx_bm bm \n" +
            "\t\twhere mx.yhid = #{yhid} and mx.jsid = #{jsid} and mx.bmm = bm.bmm")
    List<YhjsmxVO> findYhjsmxList(JcqxYhjsmx yhjsmx);

    @Select("select a.yhid,b.bmmc,b.bmm,b.sfejbm,b.pxh from jcqx_yhbm a\n" +
            "        left join jcqx_bm b on a.bmm = b.bmm\n" +
            "        where yhid = #{yhid}")
    List<JcqxBm> findBmList(@Param("yhid") String yhid);

    @Select("<script> " + "select distinct yhid,yhzt,xm\n" +
            "        from jcqx_yh\n" +
            "        where yhid not in (select yh.yhid\n" +
            "        from jcqx_yh yh, jcqx_yhjs yhjs\n" +
            "        where yhjs.jsid = #{jsglQuery.jsid}\n" +
            "        and (yh.yhid = yhjs.yhid or\n" +
            "        (yh.sfms is not null and yhjs.sfmsjh like ('%'||yh.sfms||'%'))))\n" +
            "        <if test=\"jsglQuery.yhzt != null\">\n" +
            "            and yhzt = #{jsglQuery.yhzt}\n" +
            "        </if>\n" +
            "        <if test=\"jsglQuery.idxm != null and jsglQuery.idxm != ''\">\n" +
            "            and (xm like '%${jsglQuery.idxm}%' or yhid like '%${jsglQuery.idxm}%')\n" +
            "        </if> order by yhid" + "</script>")
    IPage<YhglVO> findYhList(Page<JsglQuery> pageObj, @Param("jsglQuery") JsglQuery jsglQuery);

    @Select(" select js.jsid from\n" +
            "        (SELECT jsid,fjsid,jsmc FROM jcqx_js START WITH jsid = #{jsid} CONNECT BY jsid = PRIOR fjsid) js")
    List<String> findScjsid(@Param("jsid") String jsid);

    @Select("select yhid from jcqx_yhjs where jsid = #{jsid} and yhid = #{yhid}")
    String listJsyh(JcqxYhjs yhjs);

    @Select("select mx.id, mx.yhid, yh.xm, mx.jsid, js.jsmc, mx.bmm, bm.bmmc\n" +
            "        from jcqx_yhjsmx mx\n" +
            "        left join jcqx_yh yh on mx.yhid = yh.yhid\n" +
            "        left join jcqx_js js on mx.jsid = js.jsid\n" +
            "        left join jcqx_bm bm on mx.bmm = bm.bmm\n" +
            "        where mx.yhid = #{jsglQuery.yhid} and mx.jsid = #{jsglQuery.jsid}\n" +
            "        order by mx.cjsj")
    IPage<YhjsmxVO> getYhjsmxlist(Page<JsglQuery> pageObj, @Param("jsglQuery") JsglQuery jsglQuery);

    @Select("select count(*) from jcqx_yhbm where yhid = #{yhid} and bmm = #{bmm}")
    Integer findYhbmCount(JsglQuery jsglQuery);

    @Delete("delete jcqx_yhjs where yhid = #{yhid} and jsid = #{jsid}\n" +
            "            and (select count(*) from jcqx_yhjsmx where yhid = #{yhid} and jsid = #{jsid}) = 0")
    Integer deleteYhjsByYhjsmx(JcqxYhjsmx yhjsmx);

    @Delete("delete jcqx_yhbm where yhid = #{yhid} and bmm = #{bmm} and sfzz = 0\n" +
            "            and (select count(*) from jcqx_yhjsmx where yhid = #{yhid} and bmm = #{bmm}) = 0")
    Integer deleteYhbmByYhjsmx(JcqxYhjsmx yhjsmx);
}